﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WiSContactData.util
{
    public class DB
    {
        //public static bool connect()
        //{
        //    bool result = true;
        //    try
        //    {
        //        SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString);
        //        SqlConnection cn.Open();
        //    }
        //    catch
        //    {
        //        result = false;
        //    }
        //    return result;
        //}

        public static string getValue(string queryString)
        {
            string result = "";
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        Object obj = cmd.ExecuteScalar();
                        if (obj == null)
                            result = "";
                        else
                            result = obj.ToString();
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {

            }

            return result;
        }
        public static string getValue(string queryString, object[,] pararmeter)
        {
            string result = "";
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        if (pararmeter != null)
                            for (int i = 0; i < pararmeter.GetLength(0); i++)
                                cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
                        Object obj = cmd.ExecuteScalar();
                        if (obj == null)
                            result = "";
                        else
                            result = obj.ToString();
                    }
                    cn.Close();
                }

            }
            catch (Exception ex)
            {
            }
            return result;
        }

        public static DataTable getData(string queryString)
        {
            DataTable dt = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            dt = new DataTable();
                            dt.Load(dr);
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {

            }
            return dt;
        }

        public static DataTable getData(string queryString, object[,] pararmeter)
        {
            DataTable dt = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        if (pararmeter != null)
                            for (int i = 0; i < pararmeter.GetLength(0); i++)
                                cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            dt = new DataTable();
                            dt.Load(dr);
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {

            }
            return dt;
        }

        public static DataTable getData(string queryString, object[,] pararmeter, bool[] isUnicode)
        {
            DataTable dt = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        if (pararmeter != null)
                            for (int i = 0; i < pararmeter.GetLength(0); i++)
                            {
                                SqlParameter parameter = new SqlParameter();
                                parameter.ParameterName = pararmeter[i, 0].ToString();
                                parameter.Direction = ParameterDirection.Input;
                                parameter.Value = pararmeter[i, 1].ToString().Replace('-', ' ');
                                if (isUnicode[i] == true)
                                    parameter.SqlDbType = SqlDbType.NVarChar;
                                else
                                    parameter.SqlDbType = SqlDbType.VarChar;
                                cmd.Parameters.Add(parameter);
                            }
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            dt = new DataTable();
                            dt.Load(dr);
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {

            }
            return dt;
        }

        public static bool exec(string queryString)
        {
            bool success = true;
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                    cn.Close();
                }
            }
            catch
            {
                success = false;
            }
            return success;
        }

        public static bool exec(string queryString, object[,] pararmeter)
        {
            bool success = true;
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        cmd.CommandType = CommandType.Text;
                        if (pararmeter != null)
                            for (int i = 0; i < pararmeter.GetLength(0); i++)
                                cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
                        cmd.ExecuteNonQuery();
                    }
                    cn.Close();
                }
            }
            catch
            {
                success = false;
            }
            return success;
        }

        public static bool exec(string queryString, string commandType)
        {
            bool success = true;
            try
            {

                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        switch (commandType)
                        {
                            case "StoredProcedure":
                                cmd.CommandType = CommandType.StoredProcedure;
                                break;
                            case "TableDirect":
                                cmd.CommandType = CommandType.TableDirect;
                                break;
                            case "Text":
                                cmd.CommandType = CommandType.Text;
                                break;
                        }
                        cmd.ExecuteNonQuery();
                    }
                    cn.Close();
                }
            }
            catch
            {
                success = false;
            }
            return success;
        }

        public static bool exec(string queryString, object[,] pararmeter, string commandType)
        {
            bool success = true;
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        switch (commandType)
                        {
                            case "StoredProcedure":
                                cmd.CommandType = CommandType.StoredProcedure;
                                break;
                            case "TableDirect":
                                cmd.CommandType = CommandType.TableDirect;
                                break;
                            case "Text":
                                cmd.CommandType = CommandType.Text;
                                break;
                        }
                        if (pararmeter != null)
                            for (int i = 0; i < pararmeter.GetLength(0); i++)
                                cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
                        cmd.ExecuteNonQuery();
                    }
                    cn.Close();
                }
            }
            catch
            {
                success = false;
            }
            return success;
        }
    }
}